﻿using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;

namespace Ice.ChinaKnowledge.Data
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public class DbHelper
    {
        /// <summary>
        /// 驱动名称(默认为SQL Server数据库) 
        /// </summary>
        private string _dbProviderName = "System.Data.SqlClient";
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string _dbConnectionString = string.Empty;
        ///// <summary>
        ///// 默认构造函数
        ///// </summary>
        //public DbHelper()
        //{
        //    //==============================================
        //    //  载入默认连接字符串配置对像，默认取第0个元素
        //    //==============================================
        //    ConnectionStringSettings connectionSettings = ConfigurationManager.ConnectionStrings[ConfigurationManager.ConnectionStrings.Count - 1];
        //    //==============================================
        //    //  载入连接字符串和驱动名称
        //    //==============================================
        //    _dbConnectionString = connectionSettings.ConnectionString;
        //    if (connectionSettings.ProviderName != string.Empty)
        //    {
        //        _dbProviderName = connectionSettings.ProviderName;
        //    }
        //}
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        public DbHelper(string connectionString)
        {
            _dbConnectionString = connectionString;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="providerName">驱动名称</param>
        public DbHelper(string connectionString, string providerName)
        {
            _dbConnectionString = connectionString;
            _dbProviderName = providerName;
        }
        /// <summary>
        /// 获取事务对像
        /// </summary>
        /// <returns>DbTransaction</returns>
        public virtual DbTransaction CreateTransaction()
        {
            DbConnection dbConn = Connection;
            dbConn.Open();
            return dbConn.BeginTransaction();
        }
        /// <summary>
        /// 获取事务对像
        /// </summary>
        /// <param name="level">事务锁定行为</param>
        /// <returns>DbTransaction</returns>
        public virtual DbTransaction CreateTransaction(IsolationLevel level)
        {
            DbConnection dbConn = Connection;
            return dbConn.BeginTransaction(level);
        }
        /// <summary>
        /// 获取数据库驱动对像
        /// </summary>
        public DbProviderFactory Provider
        {
            get
            {
                return DbProviderFactories.GetFactory(_dbProviderName);
            }
        }
        /// <summary>
        /// 获取数据库连接对像
        /// </summary>
        public DbConnection Connection
        {
            get
            {
                DbConnection conn = Provider.CreateConnection();
                conn.ConnectionString = _dbConnectionString;
                return conn;
            }
        }

        #region /* Create Parameter */

        public DbParameter[] CreateParameter(DataTransfer transfer)
        {
            var dbParameters = new List<DbParameter>();

            foreach (var key in transfer.Keys())
            {
                dbParameters.Add(CreateParameter(key, transfer[key]));
            }

            return dbParameters.ToArray();
        }

        public DbParameter[] CreateParameter(System.Collections.Generic.Dictionary<string, object> parameters)
        {
            if (parameters == null) parameters = new System.Collections.Generic.Dictionary<string, object>();

            var dbParameters = new List<DbParameter>();

            foreach (var key in parameters.Keys)
            {
                dbParameters.Add(CreateParameter(key, parameters[key]));
            }

            return dbParameters.ToArray();
        }

        public DbParameter CreateParameter(string parameterName, object parameterValue)
        {
            DbParameter parameter = Provider.CreateParameter();

            parameter.ParameterName = parameterName;
            parameter.Value = parameterValue;

            return parameter;
        }

        public DbParameter CreateParameter(string parameterName, DbType dbType, int size, object parameterValue)
        {
            DbParameter parameter = Provider.CreateParameter();

            parameter.ParameterName = parameterName;
            parameter.Value = parameterValue;
            parameter.DbType = dbType;
            parameter.Size = size;

            return parameter;
        }

        public DbParameter CreateParameter(string pname, DbType dbType, string sname)
        {
            DbParameter parameter = Provider.CreateParameter();

            parameter.ParameterName = pname;
            parameter.DbType = dbType;
            parameter.SourceColumn = sname;

            return parameter;
        }

        #endregion

        #region /* Execute SQL */

        /// <summary>
        /// 批量添加数据
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="dtbl"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public int BatchUpdate(string sqlText, DataTable dtbl)
        {
            using (DbConnection conn = Connection)
            {
                DbDataAdapter adapter = Provider.CreateDataAdapter();
                DbCommand cmd = conn.CreateCommand();

                adapter.InsertCommand = cmd;

                BatchAddParams(cmd, dtbl.Columns);

                cmd.CommandText = sqlText;
                cmd.UpdatedRowSource = UpdateRowSource.None;
                adapter.UpdateBatchSize = 1000;
                int r = adapter.Update(dtbl);
                dtbl.AcceptChanges();
                return r;
            }
        }
        /// <summary>
        /// 批量添加数据
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="dtbl"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public int BatchInsert(string sqlText, DataTable dtbl)
        {
            using (DbConnection conn = Connection)
            {
                DbDataAdapter adapter = Provider.CreateDataAdapter();
                DbCommand cmd = conn.CreateCommand();

                adapter.InsertCommand = cmd;

                BatchAddParams(cmd, dtbl.Columns);

                cmd.CommandText = sqlText;
                cmd.UpdatedRowSource = UpdateRowSource.None;
                adapter.UpdateBatchSize = 1000;
                int r = adapter.Update(dtbl);
                dtbl.AcceptChanges();
                return r;
            }
        }

        private void BatchAddParams(DbCommand cmd, System.Data.DataColumnCollection columns)
        {
            if (dictDataType.Count == 0)
            {
                dictDataType.Add(typeof(string), DbType.String);
                dictDataType.Add(typeof(short), DbType.Int16);
                dictDataType.Add(typeof(int), DbType.Int32);
                dictDataType.Add(typeof(long), DbType.Int64);
                dictDataType.Add(typeof(ushort), DbType.UInt16);
                dictDataType.Add(typeof(uint), DbType.UInt32);
                dictDataType.Add(typeof(ulong), DbType.UInt64);
                dictDataType.Add(typeof(float), DbType.Single);
                dictDataType.Add(typeof(double), DbType.Double);
                dictDataType.Add(typeof(System.DateTime), DbType.DateTime);
                dictDataType.Add(typeof(byte), DbType.Byte);
            }

            for (var i = 0; i < columns.Count; i++)
            {

                var p = CreateParameter("@" + columns[i].ColumnName, dictDataType[columns[i].DataType], columns[i].ColumnName);
                cmd.Parameters.Add(p);
            }
        }

        private Dictionary<System.Type, DbType> dictDataType = new Dictionary<System.Type, DbType>();

        public void SqlBluck(DataTable dtbl, string tableName)
        {
            using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(Connection.ConnectionString))
            {
                sbc.DestinationTableName = tableName;
                sbc.WriteToServer(dtbl);
            }
        }
        /// <summary>
        /// 执行查询并返回DataSet
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sqlText, params DbParameter[] cmdParams)
        {
            using (DbConnection conn = Connection)
            {
                DbCommand cmd = conn.CreateCommand();

                PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);

                DbDataAdapter adapter = Provider.CreateDataAdapter();

                adapter.SelectCommand = cmd;

                DataSet dst = new DataSet();

                adapter.Fill(dst);

                return dst;
            }
        }
        /// <summary>
        /// 获取DbDataReader对像
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string sqlText, params DbParameter[] cmdParams)
        {
            DbConnection conn = Connection;

            DbCommand cmd = conn.CreateCommand();

            PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);

            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 获取DbDataReader对像
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="tran">事务对像</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string sqlText, DbTransaction tran, params DbParameter[] cmdParams)
        {
            DbConnection conn = tran.Connection;

            DbCommand cmd = conn.CreateCommand();
            cmd.Transaction = tran;

            PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);

            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlText, params DbParameter[] cmdParams)
        {
            using (DbConnection conn = Connection)
            {
                DbCommand cmd = conn.CreateCommand();

                PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);

                int result = cmd.ExecuteNonQuery();

                return result;
            }
        }
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="tran">事务对像</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlText, DbTransaction tran, params DbParameter[] cmdParams)
        {
            DbConnection conn = tran.Connection;
            DbCommand cmd = conn.CreateCommand();
            cmd.Transaction = tran;

            PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);

            int result = cmd.ExecuteNonQuery();

            return result;
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <typeparam name="ReturnType">返回值类型，只限基础类型。如:string,int,float...</typeparam>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public ReturnType ExecuteScalar<ReturnType>(string sqlText, params DbParameter[] cmdParams)
        {
            using (DbConnection conn = Connection)
            {
                DbCommand cmd = conn.CreateCommand();

                PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);

                object result = cmd.ExecuteScalar();

                return (ReturnType)result;
            }
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <typeparam name="ReturnType">返回值类型，只限基础类型。如:string,int,float..</typeparam>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="tran">事务对像</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns></returns>
        public ReturnType ExecuteScalar<ReturnType>(string sqlText, DbTransaction tran, params DbParameter[] cmdParams)
        {
            DbConnection conn = tran.Connection;
            DbCommand cmd = conn.CreateCommand();
            cmd.Transaction = tran;
            PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);

            object result = cmd.ExecuteScalar();

            return (ReturnType)result;
        }
        /// <summary>
        /// 执行存储过程并反回结果集
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteStoredProcedure(string sqlText, params DbParameter[] cmdParams)
        {
            using (DbConnection conn = Connection)
            {
                DbCommand cmd = conn.CreateCommand();
                DbDataAdapter adaper = Provider.CreateDataAdapter();

                PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);


                adaper.SelectCommand = cmd;

                DataSet dstResult = new DataSet();

                adaper.Fill(dstResult);

                return dstResult;
            }
        }
        /// <summary>
        /// 执行存储过程并反回结果集
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="tran">事务对像</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteStoredProcedure(string sqlText, DbTransaction tran, params DbParameter[] cmdParams)
        {

            DbConnection conn = tran.Connection;
            DbCommand cmd = conn.CreateCommand();
            cmd.Transaction = tran;
            DbDataAdapter adaper = Provider.CreateDataAdapter();

            PrepareCommand(conn, cmd, CommandType.Text, sqlText, cmdParams);

            adaper.SelectCommand = cmd;

            DataSet dstResult = new DataSet();

            adaper.Fill(dstResult);

            return dstResult;
        }

        public DataTransfer SingleDataTransfer(string sqlText, params DbParameter[] cmdParams)
        {
            using (IDataReader dataReader = ExecuteReader(sqlText, cmdParams))
            {
                dataReader.Read();
                return new DataTransfer(dataReader);
            }
        }

        public DataTransfer SingleDataTransfer(string sqlText, DbTransaction tran, params DbParameter[] cmdParams)
        {
            using (IDataReader dataReader = ExecuteReader(sqlText, tran, cmdParams))
            {
                dataReader.Read();
                return new DataTransfer(dataReader);
            }
        }

        public List<DataTransfer> ArrayDataTransfer(string sqlText, params DbParameter[] cmdParams)
        {
            using (IDataReader dataReader = ExecuteReader(sqlText, cmdParams))
            {
                return DataTransferTools.ArrayDataTransferWithDataReader(dataReader).ToList();
            }
        }

        public List<DataTransfer> ArrayDataTransfer(string sqlText, DbTransaction tran, params DbParameter[] cmdParams)
        {
            using (IDataReader dataReader = ExecuteReader(sqlText, tran, cmdParams))
            {
                return DataTransferTools.ArrayDataTransferWithDataReader(dataReader).ToList();
            }
        }

        public T SingleEntity<T>(string sqlText, params DbParameter[] cmdParams)
        {
            using (IDataReader dataReader = ExecuteReader(sqlText, cmdParams))
            {
                var entity = EntityBuilder<T>.CreateInstance();

                dataReader.Read();

                (entity as DataTransfer).Fill(dataReader);

                return entity;
            }
        }

        public T SingleEntity<T>(string sqlText, DbTransaction tran, params DbParameter[] cmdParams)
        {
            using (IDataReader dataReader = ExecuteReader(sqlText, tran, cmdParams))
            {
                var entity = EntityBuilder<T>.CreateInstance();

                dataReader.Read();

                (entity as DataTransfer).Fill(dataReader);

                return entity;
            }
        }

        public List<T> ArrayEntity<T>(string sqlText, params DbParameter[] cmdParams)
        {
            using (IDataReader dataReader = ExecuteReader(sqlText, cmdParams))
            {
                return DataTransferTools.ArrayEntityWithDataReader<T>(dataReader).ToList();
            }
        }

        public List<T> ArrayEntity<T>(string sqlText, DbTransaction tran, params DbParameter[] cmdParams)
        {
            using (IDataReader dataReader = ExecuteReader(sqlText, tran, cmdParams))
            {
                return DataTransferTools.ArrayEntityWithDataReader<T>(dataReader).ToList();
            }
        }
        private void PrepareCommand(DbConnection dbConn, DbCommand dbCmd, CommandType cmdType, string sqlText, params DbParameter[] cmdParams)
        {
            if (dbConn.State != ConnectionState.Open)
            {
                dbConn.Open();
            }

            dbCmd.CommandType = cmdType;
            dbCmd.CommandText = sqlText;

            if (cmdParams != null)
            {
                foreach (DbParameter parameter in cmdParams)
                {
                    dbCmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion

    }
}
